﻿using LinkSql;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using hospital_registration_system.Entity;

namespace hospital_registration_system.DAL
{
    public class DepartmentsDao
    {
        MySqlDataAdapter mySqlDataAdapter;
        private string tableName = "departments";

        /// <summary>
        /// 通过主键id查询
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Departments selectById(int id)
        {
            return GetDepartmentsByDataTable(MySQLUtils.selectById(tableName, id))[0];
        }

        /// <summary>
        /// 通过对象插入
        /// </summary>
        /// <param name="departments"></param>
        /// <returns></returns>
        public bool insert(Departments departments)
        {
            MySqlConnection connection = MySQLUtils.GetMySQLConnection();
            try
            {
                string sql = "insert into departments(department_name,floor,house_number,created_by,created_time,updated_by,updated_time,state) values("
                    + "'" + departments.Department_name + "'" + ","
                    + "'" + departments.Floor + "'" + ","
                    + "'" + departments.House_number + "'" + ","
                    + "'" + departments.CREATED_BY1 + "'" + ","
                    + "'" + departments.CREATED_TIME1 + "'" + ","
                    + "'" + departments.UPDATED_BY1 + "'" + ","
                    + "'" + departments.UPDATED_TIME1 + "'" + ","
                    + "'" + departments.State + "'"
                    + ")";
                connection.Open();
                MySqlCommand cmd = new MySqlCommand(sql, connection);
                cmd.ExecuteNonQuery();
            }
            catch (MySqlException)
            {

                return false;
            }
            finally
            {
                connection.Close();
            }

            return true;
        }

        /// <summary>
        /// 通过主键删除
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool delete(int id)
        {
            return MySQLUtils.delete("departments", id);
        }

        /// <summary>
        /// 通过对象修改
        /// </summary>
        /// <param name="departments"></param>
        /// <returns></returns>
        public bool update(Departments departments)
        {
            MySqlConnection connection = MySQLUtils.GetMySQLConnection();
            string sql = "update departments set "
                + "department_name='" + departments.Department_name + "'," +
                "floor='" + departments.Floor + "'," +
                "house_number='" + departments.House_number + "'," +
                "created_by='" + departments.CREATED_BY1 + "'," +
                "created_time='" + departments.CREATED_TIME1 + "'," +
                "updated_by='" + departments.UPDATED_BY1 + "'," +
                "updated_time='" + departments.UPDATED_TIME1 + "'," +
                "state='" + departments.State + "' " +
                "where id = " + departments.Id
                ;
            try
            {
                connection.Open();
                MySqlCommand cmd = new MySqlCommand(sql, connection);
                cmd.ExecuteNonQuery();
            }
            catch (MySqlException)
            {
                return false;
            }
            finally
            {
                connection.Close();
            }
            return true;
        }

        /// <summary>
        /// 查询所有
        /// </summary>
        /// <returns>List集合</returns>
        public List<Departments> selectAll()
        {
            return GetDepartmentsByDataTable(MySQLUtils.selectAll(tableName));
        }

        /// <summary>
        /// 通过map条件查询
        /// </summary>
        /// <param name="map">string,object</param>
        /// <returns>List集合</returns>
        public List<Departments> selectByMap(Dictionary<string,Object> map)
        {
            return GetDepartmentsByDataTable(MySQLUtils.selectByMap(tableName,map));
        }

        /// <summary>
        /// 将dataTable 类型转化为List集合
        /// </summary>
        /// <param name="dataTable"></param>
        /// <returns></returns>
        public List<Departments> GetDepartmentsByDataTable(DataTable dataTable)
        {
            List<Departments> departments = new List<Departments>();
            foreach (DataRow row in dataTable.Rows)
            {
                Departments department = new Departments(
                    int.Parse(row["id"].ToString()),
                    row["department_name"].ToString(),
                    int.Parse(row["floor"].ToString()),
                    int.Parse(row["house_number"].ToString()),
                    row["created_by"].ToString(),
                    (DateTime)(row["created_time"]),
                    row["updated_by"].ToString(),
                    (DateTime)(row["updated_time"]),
                    int.Parse(row["state"].ToString())
                );
                departments.Add(department);
            }
            return departments;
        }
    }
}
